﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace ADO数据库编程
{
    class SqlCommandBuilderDemo
    {
        public SqlDataAdapter createDt(out DataTable dt)
        {
            string sql = " select * from person ";
            SqlCommand sqlCommand = SqlHelper.createSqlCommand(sql, null);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            dt = new DataTable("Person");
            sqlDataAdapter.Fill(dt);
            return sqlDataAdapter;
        }

        public void TestAdd()
        {
            using (SqlConnection sqlConnection = SqlHelper.CreateConn())
            {
                string connectionString = SqlHelper.getConnStr("connSqlServer2");
                string sql = " SELECT id,name,age,sex,addr FROM person order by id asc ";
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter);
                sqlDataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); ;

                // DataTable 是一个内存数据表，对应数据库中的一张表。
                // DataSet 是一个数据集，可以包含多个DataTable。
                DataTable dt = new DataTable("Person");
                sqlDataAdapter.Fill(dt);

                // 添加
                DataRow dr = dt.NewRow();
                dr["name"] = "成龙";
                dr["age"] = 60;
                dr["sex"] = "男";
                dr["addr"] = "香港";
                dt.Rows.Add(dr);

                sqlDataAdapter.Update(dt);

                Console.WriteLine("插入成功");
            }
        }


        public void TestUpdate()
        {
            using (SqlConnection sqlConnection = SqlHelper.CreateConn())
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                string sql = " SELECT id,name,age,sex,addr FROM person order by id asc ";
                sqlDataAdapter.SelectCommand = new SqlCommand(sql, sqlConnection);
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter);
                sqlDataAdapter.UpdateCommand =
                    new SqlCommand("UPDATE person SET name = @name , sex = @sex ,addr = @addr,age = @age WHERE id = @id", sqlConnection);

                SqlParameter p1 = new SqlParameter("@id", SqlDbType.BigInt, 11, "id");
                SqlParameter p2 = new SqlParameter("@name", SqlDbType.VarChar, 10, "name");
                SqlParameter p3 = new SqlParameter("@sex", SqlDbType.VarChar, 10, "sex");
                SqlParameter p4 = new SqlParameter("@addr", SqlDbType.VarChar, 10, "addr");
                SqlParameter p5 = new SqlParameter("@age", SqlDbType.Int, 10, "age");
                sqlDataAdapter.UpdateCommand.Parameters.Add(p1);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p2);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p3);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p4);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p5);

                // DataTable 是一个内存数据表，对应数据库中的一张表。
                // DataSet 是一个数据集，可以包含多个DataTable。
                DataTable dt = new DataTable("person");
                sqlDataAdapter.Fill(dt);

                foreach (DataRow drow in dt.Rows)
                {
                    long id = (long)drow[0];
                    if (id == 8)
                    {
                        drow["name"] = "成龙1";
                        drow["sex"] = "男";
                        drow["addr"] = "香港";
                        drow["age"] = 55;
                    }
                }
                sqlDataAdapter.Update(dt);
                Console.WriteLine("更新数据成功");
            }
        }

        public void TestDelete()
        {
            using (SqlConnection sqlConnection = SqlHelper.CreateConn())
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                string sql = " SELECT id,name,age,sex,addr FROM person order by id asc ";
                sqlDataAdapter.SelectCommand = new SqlCommand(sql, sqlConnection);
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter);
                // 创建一个更新命令  
                sqlDataAdapter.DeleteCommand =
                    new SqlCommand("Delete from person WHERE id = @id", sqlConnection);
                SqlParameter p1 = new SqlParameter("@id", SqlDbType.BigInt, 11, "id");
                sqlDataAdapter.DeleteCommand.Parameters.Add(p1);
                // DataTable 是一个内存数据表，对应数据库中的一张表。
                // DataSet 是一个数据集，可以包含多个DataTable。
                DataTable dt = new DataTable("person");
                sqlDataAdapter.Fill(dt);
                foreach (DataRow drow in dt.Rows)
                {
                    long id = (long)drow[0];
                    if (id == 8)
                    {
                        drow.Delete();
                    }
                }
                sqlDataAdapter.Update(dt);
                Console.WriteLine("更新数据成功");
            }
        }
    }
}
